drop table dbo.tblResultdetailsFiles
go
CREATE TABLE dbo.tblResultdetailsFiles (
	resultdetailid int IDENTITY(1, 1) NOT NULL ,
	resultid int NOT NULL,
	name nvarchar(510),
	filename nvarchar(200),
	filesize bigint,
	ContentType nvarchar(100),
	BinaryData image,
	CONSTRAINT PK_tblResultdetailsFiles PRIMARY KEY CLUSTERED (resultdetailid),
	CONSTRAINT FK_tblResultdetailsFiles_tblResults FOREIGN KEY (resultid) REFERENCES tblResults(resultid)
	)

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE spGetFormResults 
	@formGUID varchar(50)
AS
SET NOCOUNT ON
--  drop table #pivot
-- Close getColumns
--deallocate getColumns

-- Creating temporary pivot table
create table #pivot (pivotid INT PRIMARY KEY)

--Createing temporary table to hold columns_names
create table #Columns (ColumnName nvarchar(255))

DECLARE @columnname nvarchar(255)
DECLARE @validform bit
DECLARE getColumns CURSOR for
select distinct name from tblResultDetails, tblResults 
WHERE tblResultDetails.resultid = tblResults.resultid
AND tblResults.formguid = @formGUID

DECLARE getColumnsFiles CURSOR for
select distinct name from tblResultDetailsFiles, tblResults 
WHERE tblResultDetailsFiles.resultid = tblResults.resultid
AND tblResults.formguid = @formGUID

set @validform = 0
open getColumns

FETCH NEXT FROM getColumns INTO @columnname

if @@fetch_status = 0 
begin
	set @validform = 1
	WHILE @@fetch_status = 0
	BEGIN
		if (select count(ColumnName) from #Columns where ColumnName = @columnname) = 0
			begin
				exec ('Alter table #pivot Add [' + @columnname + '] ntext null')
				insert into #Columns (ColumnName) values (@columnname)
			end
	FETCH NEXT FROM getColumns INTO @columnname
	END
end --if fetch status <> 0

-- Cleanup
Close getColumns
deallocate getColumns


--Allso Add The Files
open getColumnsFiles

FETCH NEXT FROM getColumnsFiles INTO @columnname

if @@fetch_status = 0 
begin
	set @validform = 1	
--	create table #pivot (pivotid INT PRIMARY KEY)
	WHILE @@fetch_status = 0
	BEGIN
		if (select count(ColumnName) from #Columns where ColumnName = @columnname) = 0
			begin
				exec ('Alter table #pivot Add [' + @columnname + '] ntext null')
				insert into #Columns (ColumnName) values (@columnname)
			end
	FETCH NEXT FROM getColumnsFiles INTO @columnname
	END
end --if fetch status <> 0

-- Cleanup
Close getColumnsFiles
deallocate getColumnsFiles

if @validform = 1 
begin
	-- Add data to pivot table
	
	DECLARE @name nvarchar(255)
	DECLARE @value nvarchar(4000)
	DECLARE @resultid int
	DECLARE @prevresultid int
	DECLARE @pivotid int
	
	set @prevresultid = 0
	DECLARE getData CURSOR for
	select tblResultDetails.name, tblResultDetails.value, tblResultDetails.resultid 
	from tblResultDetails, tblResults
	WHERE tblResultDetails.resultid = tblResults.resultid
	AND tblResults.formguid = @formGUID
	order by tblResultDetails.resultid
	
	declare @tempResultid varchar(50)

	open getData
	FETCH NEXT FROM getData INTO @name, @value, @resultid
	
	WHILE @@fetch_status = 0
	BEGIN	
		set @value = REPLACE(@value, '''', '''''')
		set @tempResultid = cast(@resultid as varchar)		
		if @resultid <> @prevresultid 
		begin
			set @prevresultid = @resultid				
			--exec ('insert #pivot (pivotid, [' + @name + ']) VALUES (' + @tempResultid + ',''??'')') 
			exec ('insert #pivot (pivotid, [' + @name + ']) VALUES (' + @tempResultid + ',N''' + @value + ''')') 									
			--set @pivotid = @@identity
		end
		else
		begin	
			--exec ('update #pivot set ' + @name + ' = ''' + @value + ''' WHERE pivotid = ' + @pivotid )
			exec ('update #pivot set [' + @name + '] = N''' + @value + ''' WHERE pivotid = ' + @tempResultid )
		end
				
	        -- set @strInsertSQL = @strInsertSQL + 'insert #pivot (' + @name + ') Values (' + @value + ') ' 
		-- exec ('insert #pivot (' + @columnname + ' text null') 
	FETCH NEXT FROM getData INTO @name, @value, @resultid
	END
	
	-- Cleanup
	Close getData
	deallocate getData

	set @prevresultid = 0
	DECLARE getDataFiles CURSOR for
	select tblResultDetailsFiles.name, '_File_' + cast(tblResultDetailsFiles.resultdetailid as varchar) + ',' + tblResultDetailsFiles.filename, tblResultDetailsFiles.resultid 
	from tblResultDetailsFiles, tblResults
	WHERE tblResultDetailsFiles.resultid = tblResults.resultid
	AND tblResults.formguid = @formGUID
	order by tblResultDetailsFiles.resultid

	set @tempResultid = ''

	open getDataFiles
	FETCH NEXT FROM getDataFiles INTO @name, @value, @resultid
	
	WHILE @@fetch_status = 0
	BEGIN	
		set @value = REPLACE(@value, '''', '''''')
		set @tempResultid = cast(@resultid as varchar)		
		if @resultid  not in (select pivotid from #pivot)--  @prevresultid 
		begin
			set @prevresultid = @resultid				
			--exec ('insert #pivot (pivotid, [' + @name + ']) VALUES (' + @tempResultid + ',''??'')') 
			exec ('insert #pivot (pivotid, [' + @name + ']) VALUES (' + @tempResultid + ',N''' + @value + ''')') 									
			--set @pivotid = @@identity
		end
		else
		begin	
			--exec ('update #pivot set ' + @name + ' = ''' + @value + ''' WHERE pivotid = ' + @pivotid )
			exec ('update #pivot set [' + @name + '] = N''' + @value + ''' WHERE pivotid = ' + @tempResultid )
		end
				
	        -- set @strInsertSQL = @strInsertSQL + 'insert #pivot (' + @name + ') Values (' + @value + ') ' 
		-- exec ('insert #pivot (' + @columnname + ' text null') 
	FETCH NEXT FROM getDataFiles INTO @name, @value, @resultid
	END
	
	-- Cleanup
	Close getDataFiles
	deallocate getDataFiles
	

	-- Return data
	select r.resultdate, p.* from #pivot p, tblResults r
	where p.pivotid = r.resultid 
	AND r.formguid = @formGUID
	order by r.resultdate desc
	
			
end --if validform
else
begin
	-- this will return 0 records...
	select distinct name from tblResultDetails, tblResults 
	WHERE tblResultDetails.resultid = tblResults.resultid
	AND tblResults.formguid = @formGUID
end
drop table #pivot
drop table #Columns

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO